/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nc.kyiv.yellow.sociallibrary.dao;

import com.nc.kyiv.yellow.sociallibrary.ConnectionManager.ConnectionManager;
import com.nc.kyiv.yellow.sociallibrary.POJO.LibUser;
import com.nc.kyiv.yellow.sociallibrary.constants.DataBaseConstants;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import com.nc.kyiv.yellow.sociallibrary.specificDaoInterface.IUserDAO;
import java.sql.Date;

/**
 *
 * @author My
 */
public class UserDAO implements IUserDAO {

    @Override
    public LibUser getUserByLoginAndPass(Map params) {
        Connection conn = null;
        LibUser user = null;
        String query = "SELECT id, role, login, first_name, "
                + " pass FROM lib_user WHERE login = ? AND pass = ?";
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, params.get("login").toString());
            preparedStatement.setString(2, params.get("pass").toString());

            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
                user = new LibUser(rs.getString("login"), 
                        rs.getString("first_name"), rs.getString("pass"));
                user.setRole((int) rs.getDouble("role"));
                user.setId((int) rs.getDouble("id"));
            } else {
                user = null;
            }
        } catch (SQLException ex) {
            System.out.println("Something is wrong in getUserByLoginAndPass method" + ex.getMessage());
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
        return user;
    }
  
    @Override
    public void insertOrUpdate(LibUser user) throws SQLException, Exception {
        Map loginAndPass = new HashMap<String, String>();
        loginAndPass.put("login", user.getLogin());
        loginAndPass.put("pass", user.getPass());

        if (getUserByLoginAndPass(loginAndPass) != null) {
            update(user);
        } else {
            save(user); 
        }
    }

    @Override
    public void save(LibUser user) throws Exception {
        Connection conn = null;

        String query = "INSERT INTO lib_user (role, login, last_name, first_name, pass, email, gender, birthday, status_id, reg_date)"
                + "VALUES("
                + "?, ?, ?, ?, ?, ?, ?, ?, ?, " // role, login, last_name, first_name, pass, email, gender, birthday, status_id
                + "TO_DATE(sysdate, 'DD.MM.RRRR'))";//reg_date

        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setInt(1, DataBaseConstants.getRole().get("beginner"));
            preparedStatement.setString(2, user.getLogin());
            preparedStatement.setString(3, user.getLastName());
            preparedStatement.setString(4, user.getFirstName());
            preparedStatement.setString(5, user.getPass());
            preparedStatement.setString(6, user.getEmail());
            preparedStatement.setInt(7, user.getGender());
            if (user.getBirthDate() != null) {
                preparedStatement.setDate(8, new java.sql.Date(user.getBirthDate().getTime()));
            } else {
                preparedStatement.setNull(8, java.sql.Types.NULL);
            }
            preparedStatement.setInt(9, DataBaseConstants.getUserStatus().get("active"));

            preparedStatement.execute();
            System.out.println("User saved");
        } catch (SQLException ex) {
            if (ex.getMessage().indexOf("unique constraint") != -1) {
                System.out.println("User with this login or pass already exists");
                throw new Exception("User with this login or email already exists. Error:" + ex.getMessage());
            } else {
                System.out.println("Something is wrong with Data Base. Error:" + ex.getMessage());
                throw new Exception("Something is wrong with Data Base. Error:" + ex.getMessage());
            }
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
    }

    @Override
    public void update(LibUser user) throws Exception {
        Connection conn = null;
        conn = ConnectionManager.getConnection();

        String query = "UPDATE lib_user "
                + "SET last_name = ?,"
                + "first_name = ?,"
                + "email = ?,"
                + "gender = ?," // anything could happen O_o
                + "birthday = ?"
                + "WHERE login = ?";

        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, user.getLastName());
            preparedStatement.setString(2, user.getFirstName());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setInt(4, user.getGender());
            if (user.getBirthDate() != null) {
                preparedStatement.setDate(5, new java.sql.Date(user.getBirthDate().getTime()));
            }
            preparedStatement.setString(6, user.getLogin());
            preparedStatement.execute();
        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base. Error: " + ex.getMessage());
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
    }

     @Override //Valerie Avramenko
    //delete user from database
    public void delete(LibUser user) throws Exception {
        Connection conn = null;
        //creating needed SQL-statement
        String query = ""
                + "DECLARE login_id lib_user.id%type = ?"
                + "BEGIN"
                  + "UPDATE book SET published_by = null WHERE published_by = login_id;"
                  + "DELETE FROM local_lib WHERE user_id = login_id;"
                  + "DELETE FROM rating WHERE user_id = login_id;"
                  + "DELETE FROM lib_user WHERE id = login_id;"
                + "END;";
        
        //getting a connection and executing prepared statement
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setInt(1, user.getId());
            preparedStatement.execute();
        } catch (SQLException ex) { 
            throw new Exception("Something is wrong with Data Base");
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
    }
    
    //Valerie Avramenko
    public LibUser getUserById(int id) throws Exception {
        Connection conn = null;
        LibUser user = null;
        String query = "BEGIN" + "SELECT login, first_name, last_name, e-mail, " +
                "pass, reg_date, birthday, role, status, gender" +
                "FROM lib_user WHERE id = ?; " + "END";
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setInt(1, id);
            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                user = new LibUser(rs.getString("login"), rs.getString("first_name"),
                        rs.getString("pass"), rs.getString("last_name"),
                        rs.getString("e-mail"), rs.getDate("birthday"));
                user.setRegDate(rs.getString("reg_date"));
                user.setRole(rs.getInt("role"));
                user.setStatus(rs.getInt("status"));
                user.setGender(rs.getInt("gender"));
                user.setId(id);
            } else {
                user = null;
            }
        } catch (SQLException ex) {
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
        return user;
    }
    
    //Valerie Avramenko
    //edit first name, last name, pass, e-mail of user
    public void editUserString(String login, String paramName, String paramValue) throws Exception {
        Connection conn = null;
        
        //creating needed SQL-statement
        String query = "UPDATE LIB_USER" +
                "SET " + paramName + " = '" + paramValue + "' " +
                "WHERE login = ?";
        
        //getting a connection and executing prepared statement
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, login);
            preparedStatement.execute();
        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        }
        finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        } 
    }
    
    //Valerie Avramenko
    //edit status_id (active, non active, ban)
    public void editUserStatus(String login, String newStatus) throws Exception {
        Connection conn = null;
        
        //creating needed SQL-statement
        String query = "UPDATE LIB_USER" +
                "SET status_id = " + 
                "(SELECT id FROM user_status WHERE status = '"
                    + newStatus+"')" +
                "WHERE login = ?;";
        
        //getting a connection and executing prepared statement
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, login);
            preparedStatement.execute();
        } catch (SQLException ex) { 
            throw new Exception("Something is wrong with Data Base");
        }
        finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        } 
    }
    
    //Valerie Avramenko
    //edit role (beginner, advanced, moderator)
    public void editUserRole(String login, String newRole) throws Exception {
        Connection conn = null;
        
        //creating needed SQL-statement
        String query = "UPDATE LIB_USER" +
                "SET role = " + 
                "(SELECT id FROM role WHERE role = '"
                    + newRole+"')" +
                "WHERE login = ?;";
        
        //getting a connection and executing prepared statement
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, login);
            preparedStatement.execute();
        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        }
        finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        } 
    }

    //Valerie Avramenko
    //edit birthday
    //FIND THE RIGHT MEDOTH TO GET DAY
    public void editUserDate(LibUser user, Date newDate) throws Exception {
        Connection conn = null;
        
        //creating needed SQL-statement
        String query = "UPDATE LIB_USER" +
                "SET birthday = " + 
                "TO_DATE('?.?.?', 'DD-MM-YYYY')" +
                "WHERE login = ?";
        
        //getting a connection and executing prepared statement
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setInt(1, newDate.getDay());
            preparedStatement.setInt(2, newDate.getMonth());
            preparedStatement.setInt(3, newDate.getYear());
            preparedStatement.setString(4, user.getLogin());
            preparedStatement.execute();
        } catch (SQLException ex) { 
            throw new Exception("Something is wrong with Data Base");
        }
        finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
    }
}
